<?php

/* Author: tbloc@cantho.gov.vn */

class Nhap {

//Properties
    private $NHAP_ID;
    private $BEP_ID;
    private $NHAP_SO_PHIEU_NHAP;
    private $NHAP_SO_PHIEU_INT;
    private $NHAP_NGAY_NHAP;
    private $NHAP_TONG_TIEN_PHAI_TRA;
    private $NHAP_TONG_TIEN_DA_TRA;
    private $NHAP_LY_DO_NHAP;
    private $NCC_ID;
    private $TUAN_ID;
    private $THANG_ID;
    private $IS_ACTIVE;
     private $MIN_NHAP_NGAY_NHAP;
    private $MAX_NHAP_NGAY_NHAP;
    public function getMIN_NHAP_NGAY_NHAP() {
        return $this->MIN_NHAP_NGAY_NHAP;
    }

    public function getMAX_NHAP_NGAY_NHAP() {
        return $this->MAX_NHAP_NGAY_NHAP;
    }

    public function setMIN_NHAP_NGAY_NHAP($MIN_NHAP_NGAY_NHAP) {
        $this->MIN_NHAP_NGAY_NHAP = $MIN_NHAP_NGAY_NHAP;
    }

    public function setMAX_NHAP_NGAY_NHAP($MAX_NHAP_NGAY_NHAP) {
        $this->MAX_NHAP_NGAY_NHAP = $MAX_NHAP_NGAY_NHAP;
    }


//properties set get 
    public function setNHAP_ID($NHAP_ID) {
        $this->NHAP_ID = $NHAP_ID;
    }

    public function getNHAP_ID() {
        return $this->NHAP_ID;
    }

    public function setBEP_ID($BEP_ID) {
        $this->BEP_ID = $BEP_ID;
    }

    public function getBEP_ID() {
        return $this->BEP_ID;
    }

    public function setNHAP_SO_PHIEU_INT($NHAP_SO_PHIEU_INT) {
        $this->NHAP_SO_PHIEU_INT = $NHAP_SO_PHIEU_INT;
    }

    public function getNHAP_SO_PHIEU_INT() {
        return $this->NHAP_SO_PHIEU_INT;
    }

    public function setNHAP_SO_PHIEU_NHAP($NHAP_SO_PHIEU_NHAP) {
        $this->NHAP_SO_PHIEU_NHAP = $NHAP_SO_PHIEU_NHAP;
    }

    public function getNHAP_SO_PHIEU_NHAP() {
        return $this->NHAP_SO_PHIEU_NHAP;
    }

    public function setNHAP_NGAY_NHAP($NHAP_NGAY_NHAP) {
        $this->NHAP_NGAY_NHAP = $NHAP_NGAY_NHAP;
    }

    public function getNHAP_NGAY_NHAP() {
        return $this->NHAP_NGAY_NHAP;
    }

    public function setNHAP_TONG_TIEN_PHAI_TRA($NHAP_TONG_TIEN_PHAI_TRA) {
        $this->NHAP_TONG_TIEN_PHAI_TRA = $NHAP_TONG_TIEN_PHAI_TRA;
    }

    public function getNHAP_TONG_TIEN_PHAI_TRA() {
        return $this->NHAP_TONG_TIEN_PHAI_TRA;
    }

    public function setNHAP_TONG_TIEN_DA_TRA($NHAP_TONG_TIEN_DA_TRA) {
        $this->NHAP_TONG_TIEN_DA_TRA = $NHAP_TONG_TIEN_DA_TRA;
    }

    public function getNHAP_TONG_TIEN_DA_TRA() {
        return $this->NHAP_TONG_TIEN_DA_TRA;
    }

    public function setNHAP_LY_DO_NHAP($NHAP_LY_DO_NHAP) {
        $this->NHAP_LY_DO_NHAP = $NHAP_LY_DO_NHAP;
    }

    public function getNHAP_LY_DO_NHAP() {
        return $this->NHAP_LY_DO_NHAP;
    }

    public function setNCC_ID($NCC_ID) {
        $this->NCC_ID = $NCC_ID;
    }

    public function getNCC_ID() {
        return $this->NCC_ID;
    }

    public function setTHANG_ID($THANG_ID) {
        $this->THANG_ID = $THANG_ID;
    }

    public function getTHANG_ID() {
        return $this->THANG_ID;
    }

    public function setTUAN_ID($TUAN_ID) {
        $this->TUAN_ID = $TUAN_ID;
    }

    public function getTUAN_ID() {
        return $this->TUAN_ID;
    }

    public function setIS_ACTIVE($IS_ACTIVE) {
        $this->IS_ACTIVE = $IS_ACTIVE;
    }

    public function getIS_ACTIVE() {
        return $this->IS_ACTIVE;
    }

//crud methods
    public function insert($db = null) {
        if ($db != null) {
            $sql = "INSERT INTO `nhap`(                        
			`NHAP_ID`, 
                        `TUAN_ID`,
                        `THANG_ID`,
				`BEP_ID`, 
				`NHAP_SO_PHIEU_NHAP`, 
                                `NHAP_SO_PHIEU_INT`,
				`NHAP_NGAY_NHAP`, 
				`NHAP_TONG_TIEN_PHAI_TRA`, 
				`NHAP_TONG_TIEN_DA_TRA`, 
				`NHAP_LY_DO_NHAP`, 
				`NCC_ID`, 
				`IS_ACTIVE` 
		)
	VALUES(
    		:NHAP_ID, 
                :TUAN_ID,
                :THANG_ID,
	    		:BEP_ID, 
	    		:NHAP_SO_PHIEU_NHAP, 
                        :NHAP_SO_PHIEU_INT,
	    		:NHAP_NGAY_NHAP, 
	    		:NHAP_TONG_TIEN_PHAI_TRA, 
	    		:NHAP_TONG_TIEN_DA_TRA, 
	    		:NHAP_LY_DO_NHAP, 
	    		:NCC_ID, 
	    		:IS_ACTIVE 
	    );";
            $parameters = array(
                ":NHAP_ID",
                ":TUAN_ID",
                ":THANG_ID",
                ":BEP_ID",
                ":NHAP_SO_PHIEU_NHAP",
                ":NHAP_SO_PHIEU_INT",
                ":NHAP_NGAY_NHAP",
                ":NHAP_TONG_TIEN_PHAI_TRA",
                ":NHAP_TONG_TIEN_DA_TRA",
                ":NHAP_LY_DO_NHAP",
                ":NCC_ID",
                ":IS_ACTIVE"
            );
            $values = array(
                $this->getNHAP_ID(),
                $this->getTUAN_ID(),
                $this->getTHANG_ID(),
                $this->getBEP_ID(),
                $this->getNHAP_SO_PHIEU_NHAP(),
                $this->getNHAP_SO_PHIEU_INT(),
                $this->getNHAP_NGAY_NHAP(),
                $this->getNHAP_TONG_TIEN_PHAI_TRA(),
                $this->getNHAP_TONG_TIEN_DA_TRA(),
                $this->getNHAP_LY_DO_NHAP(),
                $this->getNCC_ID(),
                $this->getIS_ACTIVE()
            );
            $types = array(
                PDO::PARAM_INT, //Kiem tra lai kieu du lieu nhe
                PDO::PARAM_INT,
                PDO::PARAM_INT,
                PDO::PARAM_INT, //Kiem tra lai kieu du lieu nhe
                PDO::PARAM_STR,
                PDO::PARAM_INT, //Kiem tra lai kieu du lieu nhe
                PDO::PARAM_STR, //Kiem tra lai kieu du lieu nhe
                PDO::PARAM_STR, //Kiem tra lai kieu du lieu nhe
                PDO::PARAM_STR, //Kiem tra lai kieu du lieu nhe
                PDO::PARAM_STR, //Kiem tra lai kieu du lieu nhe
                PDO::PARAM_INT, //Kiem tra lai kieu du lieu nhe
                PDO::PARAM_INT, //Kiem tra lai kieu du lieu nhe
            );
            $lastInsertID = $db->executeNonQuery($sql, $parameters, $values, $types, true);
            $this->setNHAP_ID($lastInsertID);
        }
    }

    public function delete($db = null) {
        if ($db != null) {
            $sql = "update `nhap`
	                set `is_active`=0
	                where `NHAP_ID` = :NHAP_ID;";
            $parameters = array(":NHAP_ID");
            $values = array($this->getNHAP_ID());
            $types = array(PDO::PARAM_INT); //Kiem tra lai kieu du lieu nhe
            $result = $db->executeNonQuery($sql, $parameters, $values, $types);
            return $result;
        }
    }

    public function update($db = null) {
        if ($db != null) {
            $sql = "UPDATE `nhap` SET 
			`NHAP_ID`=:NHAP_ID,
				`BEP_ID`=:BEP_ID,
				`NHAP_NGAY_NHAP`=:NHAP_NGAY_NHAP,
				`NHAP_TONG_TIEN_PHAI_TRA`=:NHAP_TONG_TIEN_PHAI_TRA,
				`NHAP_TONG_TIEN_DA_TRA`=:NHAP_TONG_TIEN_DA_TRA,
				`NHAP_LY_DO_NHAP`=:NHAP_LY_DO_NHAP,
				`NCC_ID`=:NCC_ID,
				`IS_ACTIVE`=:IS_ACTIVE
			WHERE `NHAP_ID` = :NHAP_ID;";
            $parameters = array(
                ":NHAP_ID",
                ":BEP_ID",
                ":NHAP_NGAY_NHAP",
                ":NHAP_TONG_TIEN_PHAI_TRA",
                ":NHAP_TONG_TIEN_DA_TRA",
                ":NHAP_LY_DO_NHAP",
                ":NCC_ID",
                ":IS_ACTIVE"
            );
            // Kiem tra dau , nhe
            $values = array(
                $this->getNHAP_ID(),
                $this->getBEP_ID(),
                $this->getNHAP_NGAY_NHAP(),
                $this->getNHAP_TONG_TIEN_PHAI_TRA(),
                $this->getNHAP_TONG_TIEN_DA_TRA(),
                $this->getNHAP_LY_DO_NHAP(),
                $this->getNCC_ID(),
                $this->getIS_ACTIVE()
            );
            $types = array(
                PDO::PARAM_INT, //Kiem tra lai kieu du lieu nhe
                PDO::PARAM_INT, //Kiem tra lai kieu du lieu nhe
                PDO::PARAM_STR, //Kiem tra lai kieu du lieu nhe
                PDO::PARAM_STR, //Kiem tra lai kieu du lieu nhe
                PDO::PARAM_STR, //Kiem tra lai kieu du lieu nhe
                PDO::PARAM_STR, //Kiem tra lai kieu du lieu nhe
                PDO::PARAM_INT, //Kiem tra lai kieu du lieu nhe
                PDO::PARAM_INT, //Kiem tra lai kieu du lieu nhe
            );
            $result = $db->executeNonQuery($sql, $parameters, $values, $types);
            return $result;
        }
    }

    public function getByID($db = null) {
        if ($db != null) {
            $sql = "SELECT
			`NHAP_ID`,
                         `TUAN_ID`,
                        `THANG_ID`,
				`BEP_ID`,
				`NHAP_SO_PHIEU_NHAP`,
				`NHAP_NGAY_NHAP`,
				`NHAP_TONG_TIEN_PHAI_TRA`,
				`NHAP_TONG_TIEN_DA_TRA`,
				`NHAP_LY_DO_NHAP`,
				`NCC_ID`,
				`IS_ACTIVE`
			FROM `nhap` WHERE `NHAP_ID` = :NHAP_ID and `is_active`=1;";
            $parameters = array(":NHAP_ID");
            $values = array($this->getNHAP_ID());
            $types = array(PDO::PARAM_INT); //kiem tra lai kieu du lieu nhe
            $rows = $db->executeQuery($sql, $parameters, $values, $types);
            if ($rows != null) {
                $row = $rows->fetch();
                $this->setNHAP_ID($row->NHAP_ID);
                $this->setTUAN_ID($row->TUAN_ID);
                $this->setTHANG_ID($row->THANG_ID);
                $this->setBEP_ID($row->BEP_ID);
                $this->setNHAP_SO_PHIEU_NHAP($row->NHAP_SO_PHIEU_NHAP);
                $this->setNHAP_NGAY_NHAP($row->NHAP_NGAY_NHAP);
                $this->setNHAP_TONG_TIEN_PHAI_TRA($row->NHAP_TONG_TIEN_PHAI_TRA);
                $this->setNHAP_TONG_TIEN_DA_TRA($row->NHAP_TONG_TIEN_DA_TRA);
                $this->setNHAP_LY_DO_NHAP($row->NHAP_LY_DO_NHAP);
                $this->setNCC_ID($row->NCC_ID);
                $this->setIS_ACTIVE($row->IS_ACTIVE);
            }
            return $this;
        }
    }

    public function getMaxID($db = null) {
        if ($db != null) {
            $sql = "SELECT MAX(`nhap`.`NHAP_SO_PHIEU_INT`) AS NHAP_SO_PHIEU_INT, YEAR(CURRENT_DATE) YEAR "
                    . "FROM `nhap` "
                    . "WHERE `nhap`.`IS_ACTIVE`=1 AND YEAR(`nhap`.`NHAP_NGAY_NHAP`)=YEAR(CURRENT_DATE)";
            $parameters = array();
            $values = array();
            $types = array(); //kiem tra lai kieu du lieu nhe
            $rows = $db->executeQuery($sql, $parameters, $values, $types);
            if ($rows != null) {
                $row = $rows->fetch();
                $this->setNHAP_SO_PHIEU_INT($row->NHAP_SO_PHIEU_INT);
                $this->setNHAP_SO_PHIEU_NHAP($row->YEAR);
            }
            return $this;
        }
    }

    public function getMaxID_($db = null) {
        if ($db != null) {
            $sql = "SELECT MAX(`nhap`.`NHAP_ID`) AS  NHAP_ID,`nhap`.`NHAP_NGAY_NHAP`,`nhap`.`TUAN_ID`,`nhap`.`THANG_ID` FROM `nhap` WHERE `nhap`.`IS_ACTIVE`=1";
            $parameters = array();
            $values = array();
            $types = array(); //kiem tra lai kieu du lieu nhe
            $rows = $db->executeQuery($sql, $parameters, $values, $types);
            if ($rows != null) {
                $row = $rows->fetch();
                $this->setNHAP_ID($row->NHAP_ID);
                $this->setNHAP_NGAY_NHAP($row->NHAP_NGAY_NHAP);
                $this->setTUAN_ID($row->TUAN_ID);
                $this->setTHANG_ID($row->THANG_ID);
            }
            return $this;
        }
    }

    public function getAllThang($db = null) {
        if ($db != null) {
            $sql = "SELECT DISTINCT `THANG_ID`
    FROM `nhap`  WHERE `is_active`=1 ORDER BY `THANG_ID`;";
            $rows = $db->executeQuery($sql);
            $objects = array();
            if ($rows != null) {
                while ($row = $rows->fetch()) {
                    $obj = new Nhap();
                    $obj->setTHANG_ID($row->THANG_ID);
                    $objects[] = $obj;
                }
                return $objects;
            }
        }
    }

    
    
     public function getMaxTuan($db = null) {
        if ($db != null) {
            $sql = "SELECT MAX(`nhap`.`TUAN_ID`) AS  TUAN_ID FROM `nhap` WHERE `THANG_ID`=:THANG_ID AND `nhap`.`IS_ACTIVE`=1";
             $parameters = array(":THANG_ID");
            $values = array($this->getTHANG_ID());
            $types = array(PDO::PARAM_INT); //kiem tra lai kieu du lieu nhe
            $rows = $db->executeQuery($sql, $parameters, $values, $types);
            if ($rows != null) {
                $row = $rows->fetch();
                $this->setTUAN_ID($row->TUAN_ID);
            }
            return $this;
        }
    }
    
    
    
    public function getMaxThang($db = null) {
        if ($db != null) {
            $sql = "SELECT MAX(`nhap`.`THANG_ID`) AS  THANG_ID FROM `nhap` WHERE `nhap`.`IS_ACTIVE`=1";
            $parameters = array();
            $values = array();
            $types = array(); //kiem tra lai kieu du lieu nhe
            $rows = $db->executeQuery($sql, $parameters, $values, $types);
            if ($rows != null) {
                $row = $rows->fetch();
                $this->setTHANG_ID($row->THANG_ID);
            }
            return $this;
        }
    }
     public function showdulieu($idbep,$thang,$tuan,$db = null) {
        if ($db != null) {
            $sql = "SELECT SUM(B.TONG_TIEN) AS TONG_TIEN, MAX(B.NGAYMAX) AS MAX_NHAP_NGAY_NHAP , MIN(B.NGAYMIN) AS MIN_NHAP_NGAY_NHAP
                    FROM 
                    (
                    SELECT SUM(SUMCHIPHIEUNHAP)  AS TONG_TIEN , MAX(NGAYCHI) AS NGAYMAX , MIN(NGAYCHI) AS NGAYMIN
                    FROM 
                    (
                    -- THỐNG KÊ CHI NHẬP HÀNG
                    SELECT `NHAP_SO_PHIEU_NHAP`,SUM(`CHI_SO_TIEN`) AS 'SUMCHIPHIEUNHAP', `NHAP_NGAY_NHAP`,`NHAP_TONG_TIEN_DA_TRA`,`TUAN_ID`,`THANG_ID`,`BEP_ID`, c.`CHI_NGAY_CHI` AS `NGAYCHI` 
                    FROM `nhap` AS n JOIN `chi_nhap_hang` cnh JOIN `chi` c
                    ON cnh.`NHAP_ID` = n.`NHAP_ID` AND cnh.`CHI_ID` = c.`CHI_ID`
                    GROUP BY cnh.`NHAP_ID`) A
                    WHERE A.tuan_id = ".$tuan." AND A.thang_id = ".$thang." AND A.BEP_ID = ".$idbep."

                    UNION

                    SELECT SUM(SUMCHIHOAHONG)  AS TONG_TIEN , MAX(NGAYCHI) AS NGAYMAX , MIN(NGAYCHI) AS NGAYMIN
                    FROM 
                    (
                    -- THỐNG KÊ CHI HOA HỒNG
                    SELECT `xuat`.`XUAT_SO_PHIEU_XUAT`,SUM(`chi`.`CHI_SO_TIEN`) AS 'SUMCHIHOAHONG',`TUAN_ID`,`THANG_ID`,`bep`.`BEP_ID` ,`chi`.`CHI_NGAY_CHI` AS `NGAYCHI` 
                    FROM `xuat` JOIN `tra_hoa_hong` JOIN `chi_hoa_hong` JOIN `chi` JOIN `truong` JOIN `bep`
                    ON `xuat`.`XUAT_ID` = `tra_hoa_hong`.`XUAT_ID` AND `tra_hoa_hong`.`THH_ID` = `chi_hoa_hong`.`THH_ID` AND `chi_hoa_hong`.`CHI_ID`=`chi`.`CHI_ID`
                    AND `xuat`.`TRUONG_ID` = `truong`.`TRUONG_ID` AND  `truong`.`BEP_ID` = `bep`.`BEP_ID`
                    GROUP BY `tra_hoa_hong`.`XUAT_ID`) A
                     WHERE A.tuan_id = ".$tuan." AND A.thang_id = ".$thang." AND A.BEP_ID = ".$idbep."
                    
                      UNION 
                      SELECT SUM(SUMCHITHUE)  AS TONG_TIEN , MAX(NGAYCHI) AS NGAYMAX , MIN(NGAYCHI) AS NGAYMIN
                    FROM 
                    (
                    SELECT `xuat`.`XUAT_SO_PHIEU_XUAT`,SUM(`chi`.`CHI_SO_TIEN`) AS 'SUMCHITHUE',`TUAN_ID`,`THANG_ID`,`bep`.`BEP_ID` ,`chi`.`CHI_NGAY_CHI` AS `NGAYCHI` 
                    FROM `xuat` JOIN `tra_thue` JOIN `chi_thue` JOIN `chi` JOIN `truong` JOIN `bep`
                    ON `xuat`.`XUAT_ID` = `tra_thue`.`XUAT_ID` AND `chi_thue`.`CHI_ID` = `chi_thue`.`CHI_ID` AND  `chi_thue`.`CHI_ID`=`chi`.`CHI_ID`
                    AND `xuat`.`TRUONG_ID` = `truong`.`TRUONG_ID` AND  `truong`.`BEP_ID` = `bep`.`BEP_ID`
                    GROUP BY `tra_thue`.`XUAT_ID`) A
                    WHERE A.tuan_id = ".$tuan." AND A.thang_id = ".$thang." AND A.BEP_ID = ".$idbep."                    
                    ) B";
           $parameters = array();
            $values = array();
            $types = array(); //kiem tra lai kieu du lieu nhe
            $rows = $db->executeQuery($sql, $parameters, $values, $types);
            if ($rows != null) {
                $row = $rows->fetch();
                $this->setNHAP_TONG_TIEN_PHAI_TRA($row->TONG_TIEN);
                $this->setMAX_NHAP_NGAY_NHAP($row->MAX_NHAP_NGAY_NHAP);
                $this->setMIN_NHAP_NGAY_NHAP($row->MIN_NHAP_NGAY_NHAP);
            }
            return $this;
        }
    }
     public function showdulieu_thang($idbep,$thang,$db = null) {
        if ($db != null) {
            $sql = "SELECT SUM(B.TONG_TIEN) AS TONG_TIEN, MAX(B.NGAYMAX) AS MAX_NHAP_NGAY_NHAP , MIN(B.NGAYMIN) AS MIN_NHAP_NGAY_NHAP
                    FROM 
                    (
                    SELECT SUM(SUMCHIPHIEUNHAP)  AS TONG_TIEN , MAX(NGAYCHI) AS NGAYMAX , MIN(NGAYCHI) AS NGAYMIN
                    FROM 
                    (
                    -- THỐNG KÊ CHI NHẬP HÀNG
                    SELECT `NHAP_SO_PHIEU_NHAP`,SUM(`CHI_SO_TIEN`) AS 'SUMCHIPHIEUNHAP', `NHAP_NGAY_NHAP`,`NHAP_TONG_TIEN_DA_TRA`,`TUAN_ID`,`THANG_ID`,`BEP_ID`, c.`CHI_NGAY_CHI` AS `NGAYCHI` 
                    FROM `nhap` AS n JOIN `chi_nhap_hang` cnh JOIN `chi` c
                    ON cnh.`NHAP_ID` = n.`NHAP_ID` AND cnh.`CHI_ID` = c.`CHI_ID`
                    GROUP BY cnh.`NHAP_ID`) A
                    WHERE A.thang_id = ".$thang." AND A.BEP_ID = ".$idbep."

                    UNION

                    SELECT SUM(SUMCHIHOAHONG)  AS TONG_TIEN , MAX(NGAYCHI) AS NGAYMAX , MIN(NGAYCHI) AS NGAYMIN
                    FROM 
                    (
                    -- THỐNG KÊ CHI HOA HỒNG
                    SELECT `xuat`.`XUAT_SO_PHIEU_XUAT`,SUM(`chi`.`CHI_SO_TIEN`) AS 'SUMCHIHOAHONG',`TUAN_ID`,`THANG_ID`,`bep`.`BEP_ID` ,`chi`.`CHI_NGAY_CHI` AS `NGAYCHI` 
                    FROM `xuat` JOIN `tra_hoa_hong` JOIN `chi_hoa_hong` JOIN `chi` JOIN `truong` JOIN `bep`
                    ON `xuat`.`XUAT_ID` = `tra_hoa_hong`.`XUAT_ID` AND `tra_hoa_hong`.`THH_ID` = `chi_hoa_hong`.`THH_ID` AND `chi_hoa_hong`.`CHI_ID`=`chi`.`CHI_ID`
                    AND `xuat`.`TRUONG_ID` = `truong`.`TRUONG_ID` AND  `truong`.`BEP_ID` = `bep`.`BEP_ID`
                    GROUP BY `tra_hoa_hong`.`XUAT_ID`) A
                    WHERE A.thang_id = ".$thang." AND A.BEP_ID = ".$idbep."
                    
                      UNION 
                      SELECT SUM(SUMCHITHUE)  AS TONG_TIEN , MAX(NGAYCHI) AS NGAYMAX , MIN(NGAYCHI) AS NGAYMIN
                    FROM 
                    (
                    SELECT `xuat`.`XUAT_SO_PHIEU_XUAT`,SUM(`chi`.`CHI_SO_TIEN`) AS 'SUMCHITHUE',`TUAN_ID`,`THANG_ID`,`bep`.`BEP_ID` ,`chi`.`CHI_NGAY_CHI` AS `NGAYCHI` 
                    FROM `xuat` JOIN `tra_thue` JOIN `chi_thue` JOIN `chi` JOIN `truong` JOIN `bep`
                    ON `xuat`.`XUAT_ID` = `tra_thue`.`XUAT_ID` AND `chi_thue`.`CHI_ID` = `chi_thue`.`CHI_ID` AND  `chi_thue`.`CHI_ID`=`chi`.`CHI_ID`
                    AND `xuat`.`TRUONG_ID` = `truong`.`TRUONG_ID` AND  `truong`.`BEP_ID` = `bep`.`BEP_ID`
                    GROUP BY `tra_thue`.`XUAT_ID`) A
                    WHERE A.thang_id = ".$thang." AND A.BEP_ID = ".$idbep."                   
                    ) B";
           $parameters = array();
            $values = array();
            $types = array(); //kiem tra lai kieu du lieu nhe
            $rows = $db->executeQuery($sql, $parameters, $values, $types);
            if ($rows != null) {
                $row = $rows->fetch();
                $this->setNHAP_TONG_TIEN_PHAI_TRA($row->TONG_TIEN);
                 $this->setMAX_NHAP_NGAY_NHAP($row->MAX_NHAP_NGAY_NHAP);
                $this->setMIN_NHAP_NGAY_NHAP($row->MIN_NHAP_NGAY_NHAP);
            }
            return $this;
        }
    }
     public function showdulieu_tungay_denngay($BEP_ID,$tungay, $denngay,$db = null) {
        if ($db != null) {          
            $sql = "SELECT SUM(B.TONG_TIEN) AS TONG_TIEN, B.`BEP_ID`
                    FROM 
                    (
                    SELECT SUM(SUMCHIPHIEUNHAP)  AS TONG_TIEN , MAX(NGAYCHI) AS NGAYMAX , MIN(NGAYCHI) AS NGAYMIN,`BEP_ID`
                    FROM 
                    (
                    -- THỐNG KÊ CHI NHẬP HÀNG
                    SELECT `NHAP_SO_PHIEU_NHAP`,SUM(`CHI_SO_TIEN`) AS 'SUMCHIPHIEUNHAP', `NHAP_NGAY_NHAP`,`NHAP_TONG_TIEN_DA_TRA`,`TUAN_ID`,`THANG_ID`,`BEP_ID`, c.`CHI_NGAY_CHI` AS `NGAYCHI` 
                    FROM `nhap` AS n JOIN `chi_nhap_hang` cnh JOIN `chi` c
                    ON cnh.`NHAP_ID` = n.`NHAP_ID` AND cnh.`CHI_ID` = c.`CHI_ID`
                    GROUP BY cnh.`NHAP_ID`) A
                   WHERE `NGAYCHI`>='".$tungay."' AND `NGAYCHI`<='".$denngay."' AND `BEP_ID` = ".$BEP_ID."

                    UNION

                    SELECT SUM(SUMCHIHOAHONG)  AS TONG_TIEN , MAX(NGAYCHI) AS NGAYMAX , MIN(NGAYCHI) AS NGAYMIN,`BEP_ID`
                    FROM 
                    (
                    -- THỐNG KÊ CHI HOA HỒNG
                    SELECT `xuat`.`XUAT_SO_PHIEU_XUAT`,SUM(`chi`.`CHI_SO_TIEN`) AS 'SUMCHIHOAHONG',`TUAN_ID`,`THANG_ID`,`bep`.`BEP_ID` ,`chi`.`CHI_NGAY_CHI` AS `NGAYCHI` 
                    FROM `xuat` JOIN `tra_hoa_hong` JOIN `chi_hoa_hong` JOIN `chi` JOIN `truong` JOIN `bep`
                    ON `xuat`.`XUAT_ID` = `tra_hoa_hong`.`XUAT_ID` AND `tra_hoa_hong`.`THH_ID` = `chi_hoa_hong`.`THH_ID` AND `chi_hoa_hong`.`CHI_ID`=`chi`.`CHI_ID`
                    AND `xuat`.`TRUONG_ID` = `truong`.`TRUONG_ID` AND  `truong`.`BEP_ID` = `bep`.`BEP_ID`
                    GROUP BY `tra_hoa_hong`.`XUAT_ID`) A
                   WHERE `NGAYCHI`>='".$tungay."' AND `NGAYCHI`<='".$denngay."' AND `BEP_ID` = ".$BEP_ID."
                    
                      UNION 
                      SELECT SUM(SUMCHITHUE)  AS TONG_TIEN , MAX(NGAYCHI) AS NGAYMAX , MIN(NGAYCHI) AS NGAYMIN,`BEP_ID`
                    FROM
                    (
                    SELECT `xuat`.`XUAT_SO_PHIEU_XUAT`,SUM(`chi`.`CHI_SO_TIEN`) AS 'SUMCHITHUE',`TUAN_ID`,`THANG_ID`,`bep`.`BEP_ID` ,`chi`.`CHI_NGAY_CHI` AS `NGAYCHI` 
                    FROM `xuat` JOIN `tra_thue` JOIN `chi_thue` JOIN `chi` JOIN `truong` JOIN `bep`
                    ON `xuat`.`XUAT_ID` = `tra_thue`.`XUAT_ID` AND `chi_thue`.`CHI_ID` = `chi_thue`.`CHI_ID` AND  `chi_thue`.`CHI_ID`=`chi`.`CHI_ID`
                    AND `xuat`.`TRUONG_ID` = `truong`.`TRUONG_ID` AND  `truong`.`BEP_ID` = `bep`.`BEP_ID`
                    GROUP BY `tra_thue`.`XUAT_ID`) A
                    WHERE `NGAYCHI`>='".$tungay."' AND `NGAYCHI`<='".$denngay."' AND `BEP_ID` = ".$BEP_ID."       
                    ) B
";
           $parameters = array();
            $values = array();
            $types = array(); //kiem tra lai kieu du lieu nhe
            $rows = $db->executeQuery($sql, $parameters, $values, $types);
            if ($rows != null) {
                $row = $rows->fetch();
                $this->setNHAP_TONG_TIEN_PHAI_TRA($row->TONG_TIEN);
                 $this->setBEP_ID($row->BEP_ID);
            }
            return $this;
        }
    }
    
    public function getList($db = null) {
        if ($db != null) {
            $sql = "SELECT
			`NHAP_ID`,
				`BEP_ID`,
				`NHAP_SO_PHIEU_NHAP`,
				`NHAP_NGAY_NHAP`,
				`NHAP_TONG_TIEN_PHAI_TRA`,
				`NHAP_TONG_TIEN_DA_TRA`,
				`NHAP_LY_DO_NHAP`,
				`NCC_ID`,
				`IS_ACTIVE`
			FROM `nhap` WHERE `is_active`=1 ORDER BY NHAP_ID DESC;";
            $rows = $db->executeQuery($sql);
            $objects = array();
            if ($rows != null) {
                while ($row = $rows->fetch()) {
                    $obj = new Nhap();
                    $obj->setNHAP_ID($row->NHAP_ID);
                    $obj->setBEP_ID($row->BEP_ID);
                    $obj->setNHAP_SO_PHIEU_NHAP($row->NHAP_SO_PHIEU_NHAP);
                    $obj->setNHAP_NGAY_NHAP($row->NHAP_NGAY_NHAP);
                    $obj->setNHAP_TONG_TIEN_PHAI_TRA($row->NHAP_TONG_TIEN_PHAI_TRA);
                    $obj->setNHAP_TONG_TIEN_DA_TRA($row->NHAP_TONG_TIEN_DA_TRA);
                    $obj->setNHAP_LY_DO_NHAP($row->NHAP_LY_DO_NHAP);
                    $obj->setNCC_ID($row->NCC_ID);
                    $obj->setIS_ACTIVE($row->IS_ACTIVE);
                    $objects[] = $obj;
                }
                return $objects;
            }
        }
    }

}
?>

